﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;

namespace BusinessCloud
{
    class SqlHelp
    {
        public DataSet runSQL(string SQL)
        {
            DataTable sqldt = new DataTable();
            sqldt.Columns.Add("sql", typeof(string));
            sqldt.Rows.Clear();
            DataRow drx = sqldt.NewRow();
            drx["sql"] = SQL;
            sqldt.Rows.Add(drx);
            byte[] bts = GetBinaryFormatData(sqldt);
            WebService.MyService ms=new WebService.MyService();
            return (DataSet)RetrieveObject(ms.selectall(bts));
        }

        public DataSet creatJG()
        {
            DataSet ds = new DataSet();
            DataTable dt1 = new DataTable();
            dt1.TableName = "DBMC";
            dt1.Columns.Add("bid", typeof(string));//bid 即当前操作的别名；
            dt1.Columns.Add("bmc", typeof(string));//表名称
            dt1.Columns.Add("blx", typeof(string));//XTBM为系统表(指查询系统表)，FABM为方案表(指查询方案表)
            dt1.Columns.Add("TableName", typeof(string));//放操作标识 select,update,insert,delete 
            dt1.Columns.Add("on", typeof(object));
            ds.Tables.Add(dt1);
            DataTable dt4 = new DataTable();
            dt4.TableName = "insert";
            dt4.Columns.Add("bid", typeof(string));//插入：bid=1
            dt4.Columns.Add("zdm", typeof(string));
            dt4.Columns.Add("nr", typeof(object));//内容列为obj类型， 方便sql语句对数据类型的转换
            ds.Tables.Add(dt4);
            DataTable dt2 = new DataTable();
            dt2.TableName = "update";
            dt2.Columns.Add("bid", typeof(string));
            dt2.Columns.Add("zdm", typeof(string));
            dt2.Columns.Add("gx", typeof(string));
            dt2.Columns["gx"].DefaultValue = "=";//默认值=   坐标时：= + -
            dt2.Columns.Add("nr", typeof(object));
            ds.Tables.Add(dt2);
            //查询，修改，删除where条件
            DataTable dt3 = new DataTable();
            dt3.TableName = "where";
            dt3.Columns.Add("bid", typeof(string));//查询：bid=100
            dt3.Columns.Add("zkh", typeof(string));//左括号：(
            dt3.Columns.Add("zdm", typeof(string));//字段名
            //dt3.Columns.Add("type", typeof(string));//字段类型：n:数字；c:字符串；d:日期
            dt3.Columns.Add("djz", typeof(string));//等价值：(大于、大于等于、小于、小于等于、等于、包含)
            dt3.Columns.Add("nr", typeof(object));//查询字段的值
            dt3.Columns.Add("ykh", typeof(string));//右括号：）
            dt3.Columns.Add("gx", typeof(string));//关系：（并且、或者）
            ds.Tables.Add(dt3);
            DataTable dt6 = new DataTable();
            dt6.TableName = "selectnext";
            dt6.Columns.Add("bid", typeof(string));//查询：bid=100
            dt6.Columns.Add("zdm", typeof(string));
            dt6.Columns.Add("xh", typeof(int));
            ds.Tables.Add(dt6);
            DataTable dt7 = new DataTable();
            dt7.TableName = "select";
            dt7.Columns.Add("bid", typeof(string));//查询：bid=100
            dt7.Columns.Add("zdm", typeof(string));
            dt7.Columns.Add("as", typeof(string));
            ds.Tables.Add(dt7);
            DataTable dt8 = new DataTable();
            dt8.TableName = "selectall";
            dt8.Columns.Add("bid", typeof(string));//查询：bid=100
            dt8.Columns.Add("zdm", typeof(string));
            ds.Tables.Add(dt8);
            return ds;
        }


        public void DBMCADD(object[] selectzdm, DataSet ds)
        {
            if (ds.Tables.IndexOf("DBMC") == -1)
            {
                DataTable dt1 = DBMC();
                ds.Tables.Add(dt1);
            }
            AddDataTable(ds.Tables["DBMC"], selectzdm);
        }

        public DataTable DBMC()
        {
            DataTable dt1 = new DataTable();
            dt1.TableName = "DBMC";
            dt1.Columns.Add("bid", typeof(string));//bid 即当前操作的别名；
            dt1.Columns.Add("bmc", typeof(string));//表名称
            dt1.Columns.Add("blx", typeof(string));//XTBM为系统表(指查询系统表)，FABM为方案表(指查询方案表)
            dt1.Columns.Add("TableName", typeof(string));//放操作标识 select,update,insert,delete,
            dt1.Columns.Add("on", typeof(object));//内存表名称（只在select使用), #：select(多表)，select设计到多表查询时给select操作赋零时表名，查询零时表select赋'#'号 ；其他操作为空
            dt1.Columns["on"].DefaultValue = "";//默认值=   坐标时：= + -
            return dt1;
        }

        public void insertAdd(string[] zdm, object[] nr, DataSet ds, string bid)
        {
            if (ds.Tables.IndexOf("insert") == -1)
            {
                DataTable dt4 = new DataTable();
                dt4.TableName = "insert";
                dt4.Columns.Add("bid", typeof(string));//插入：bid=1
                dt4.Columns.Add("zdm", typeof(string));
                dt4.Columns.Add("nr", typeof(object));//内容列为obj类型， 方便sql语句对数据类型的转换
                ds.Tables.Add(dt4);
            }

            for (int i = 0; i < zdm.Length; i++)
            {
                AddDataTable(ds.Tables["insert"], new object[] { bid, zdm[i], nr[i] });

            }
        }

        public void selectAdd(string bid, object[] selectzdm, string[] aszdm, DataSet ds)
        {
            if (ds.Tables.IndexOf("select") == -1)
            {
                DataTable dt2 = select();
                ds.Tables.Add(dt2);
            }
            for (int i = 0; i < selectzdm.Length; i++)
            {
                AddDataTable(ds.Tables["select"], new object[] { bid, selectzdm[i], aszdm[i] });
            }
        }

        private DataTable select()
        {
            DataTable dt2 = new DataTable();
            dt2.TableName = "select";
            dt2.Columns.Add("bid", typeof(string));//查询：bid=100
            dt2.Columns.Add("zdm", typeof(object));
            dt2.Columns.Add("as", typeof(object));//将传入的字段名命别名
            dt2.Columns["as"].DefaultValue = "";//默认值=   坐标时：= + -
            return dt2;
        }
        public void selectallAdd(object[] selectzdm, string[] aszdm, DataSet ds, string bid)
        {
            if (ds.Tables.IndexOf("selectall") == -1)
            {
                DataTable dt2 = new DataTable();
                dt2.TableName = "selectall";
                dt2.Columns.Add("bid", typeof(string));//查询：bid=100
                dt2.Columns.Add("zdm", typeof(object));
                dt2.Columns.Add("as", typeof(object));//将传入的字段名命别名
                dt2.Columns["as"].DefaultValue = "";//默认值=   坐标时：= + -
                ds.Tables.Add(dt2);
            }
            for (int i = 0; i < selectzdm.Length; i++)
            {
                AddDataTable(ds.Tables["selectall"], new object[] { bid, selectzdm[i], aszdm[i] });
            }
        }
        public void selectNextAdd(object[] selectzdm, DataSet ds, string bid, int xh)
        {
            if (ds.Tables.IndexOf("selectnext") == -1)
            {
                DataTable dt2 = new DataTable();
                dt2.TableName = "selectnext";
                dt2.Columns.Add("bid", typeof(string));//查询：bid=100
                dt2.Columns.Add("zdm", typeof(string));
                dt2.Columns.Add("xh", typeof(int));//序号
                ds.Tables.Add(dt2);
            }
            for (int i = 0; i < selectzdm.Length; i++)
            {
                AddDataTable(ds.Tables["selectnext"], new object[] { bid, selectzdm[i], xh });

            }
        }
        public void whereAdd(object[] wherezdm, DataSet ds)
        {
            if (ds.Tables.IndexOf("where") == -1)
            {
                //查询，修改，删除where条件
                DataTable dt3 = new DataTable();
                dt3.TableName = "where";
                dt3.Columns.Add("bid", typeof(string));//查询：bid=100
                dt3.Columns.Add("zkh", typeof(string));//左括号：(
                dt3.Columns.Add("zdm", typeof(string));//字段名
                //dt3.Columns.Add("type", typeof(string));//字段类型：n:数字；c:字符串；d:日期
                dt3.Columns.Add("djz", typeof(string));//等价值：(大于、大于等于、小于、小于等于、等于、包含)
                dt3.Columns.Add("nr", typeof(object));//查询字段的值
                dt3.Columns.Add("ykh", typeof(string));//右括号：）
                dt3.Columns.Add("gx", typeof(string));//关系：（并且、或者）
                ds.Tables.Add(dt3);
            }
            AddDataTable(ds.Tables["where"], wherezdm);
        }

        public void updateAdd(string[] zdm, string gx, object[] nr, DataSet ds, string bid)
        {
            if (ds.Tables.IndexOf("update") == -1)
            {
                DataTable dt2 = new DataTable();
                dt2.TableName = "update";
                dt2.Columns.Add("bid", typeof(string));
                dt2.Columns.Add("zdm", typeof(string));
                dt2.Columns.Add("gx", typeof(string));
                dt2.Columns["gx"].DefaultValue = "=";//默认值=   坐标时：= + -
                dt2.Columns.Add("nr", typeof(object));
                ds.Tables.Add(dt2);
            }
            for (int i = 0; i < zdm.Length; i++)
            {
                AddDataTable(ds.Tables["update"], new object[] { bid, zdm[i], gx, nr[i] });
            }
        }

        /// <summary>
        /// 给dt中灌值
        /// </summary>
        /// <param name="dt">要添加数据的DataTable</param>
        /// <param name="bl">存放字段或内容的数组object</param>
        /// <returns></returns>
        public bool AddDataTable(DataTable dt, object[] bl)
        {
            DataRow dr1 = dt.NewRow();

            if (bl.Length == dr1.ItemArray.Length)//bl的长度和dt中行的列数相等 才循环给dr中加bl的值，然后在添加到dt中
            {
                for (int i = 0; i < bl.Length; i++)
                {
                    dr1[i] = bl[i];
                }
                dt.Rows.Add(dr1);
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 将序列化后的byte[]，反序列化成object
        /// </summary>
        /// <param name="binaryData"></param>
        /// <returns></returns>
        public object RetrieveObject(byte[] binaryData)
        {
            MemoryStream memStream = new MemoryStream(binaryData);
            IFormatter brFormatter = new BinaryFormatter();
            Object obj = brFormatter.Deserialize(memStream);
            return obj;
        }
        /// <summary>
        /// 将object格式化成字节数组byte[]
        /// </summary>
        /// <param name="dsOriginal">object对象</param>
        /// <returns>字节数组</returns>
        public byte[] GetBinaryFormatData(object dsOriginal)
        {
            byte[] binaryDataResult = null;
            MemoryStream memStream = new MemoryStream();
            IFormatter brFormatter = new BinaryFormatter();
            brFormatter.Serialize(memStream, dsOriginal);
            binaryDataResult = memStream.ToArray();
            memStream.Close();
            memStream.Dispose();
            return binaryDataResult;
        }

        /// <summary>
        /// 解析WEB响应生成DataSet结构
        /// </summary>
        /// <param name="cs">WEB响应</param>
        /// <returns>DataSet</returns>
        public DataSet SlectedsToDataSet(WebService.WebSlected[] cs)
        {
            DataSet ds = new DataSet();
            DataRow dr = null;
            string bmc = "";
            for (int i = 0; i < cs.Length; i++)
            {
                if (bmc != cs[i].bmc)
                {
                    bmc = cs[i].bmc;
                    dtColumnAdd(cs, i, ds);

                }
                if (cs[i].zdm == ds.Tables[cs[i].bmc].Columns[0].ColumnName)
                {
                    dr = ds.Tables[cs[i].bmc].NewRow();
                    ds.Tables[cs[i].bmc].Rows.Add(dr);
                }
                dr[cs[i].zdm] = cs[i].nr;
            }
            return ds;
        }


        /// <summary>
        /// 解析WEB响应的列,配合SlectedsToDataSet（）方法，生成DataSet结构
        /// </summary>
        /// <param name="cs">web端返回的WebSlected[]类数组</param>
        /// <param name="k">第几个WebSlected[]数组</param>
        /// <param name="ds">要返回的DataSet</param>
        private static void dtColumnAdd(WebService.WebSlected[] cs, int k, DataSet ds)
        {
            DataTable dt = new DataTable();
            dt.TableName = cs[k].bmc;
            ds.Tables.Add(dt);
            for (int i = k; i < cs.Length; i++)
            {
                if (cs[k].bmc == cs[i].bmc && ds.Tables[cs[i].bmc].Columns.IndexOf(cs[i].zdm) == -1)
                {
                    ds.Tables[cs[i].bmc].Columns.Add(cs[i].zdm, typeof(object));
                }
                else
                {
                    break;
                }
            }
        }

        /// <summary>
        /// 解析封装WEB可识别的请求
        /// </summary>
        /// <param name="ds">客户请求</param>
        /// <returns>WebSlected[]</returns>
        public WebService.WebSlected[] DataSetToSlecteds(DataSet ds)
        {
            int x = 0;
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                x += ds.Tables[i].Columns.Count * ds.Tables[i].Rows.Count;
            }
            WebService.WebSlected[] sd = new WebService.WebSlected[x];
            x = 0;
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                {
                    for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
                    {
                        WebService.WebSlected s = new WebService.WebSlected();
                        s.bmc = ds.Tables[i].TableName;
                        s.zdm = ds.Tables[i].Columns[k].ColumnName;
                        s.nr = ds.Tables[i].Rows[j][k];//字段内容
                        sd[x] = s;
                        x++;
                    }
                }
            }
            return sd;
        }
    }
}
